package cn.may.utils;

import cn.may.domain.student.Student;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.util.ArrayList;
import java.util.List;

/**
 * Excel工具类
 *
 * @author mayuanbao
 * @date 2019-06-05 11:17
 */
public class ExcelUtil {
    /***
     * 读取Excel内容
     * @author mayuanbao
     * @date 2019-06-08 11:37
     * @param pathName pathName
     * @return java.util.List<cn.may.domain.student.Student>
     */
    public static List<Student> readExcel(String pathName) throws Exception {
        InputStream is = new FileInputStream(new File(pathName));
        Workbook workbook = null;
        String xlsx = "xlsx";
        String xls = "xls";
        if (pathName.endsWith(xlsx)) {
            //Excel2007
            workbook = new XSSFWorkbook(is);
        } else if (pathName.endsWith(xls)) {
            //Excel2003
            workbook = new HSSFWorkbook(is);
        }
        Student student;
        List<Student> list = new ArrayList<>();
        //循环工作表sheet
        if (workbook != null) {
            for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
                Sheet sheet = workbook.getSheetAt(i);
                //循环行
                for (int j = 1; j < sheet.getLastRowNum() + 1; j++) {
                    Row row = sheet.getRow(j);
                    student = new Student();
                    student.setId((int) row.getCell(0).getNumericCellValue());
                    student.setName(row.getCell(1).getStringCellValue());
                    student.setAge((int) row.getCell(2).getNumericCellValue());
                    student.setSex("男".equals(row.getCell(3).getStringCellValue()) ? 1 : 0);
                    list.add(student);
                }
            }
        }
        return list;
    }

    /**
     * 导出Excel
     *
     * @return void
     * @author mayuanbao
     * @date 2019-06-06 10:54
     */

    public static void exportExcel(List<Student> students, String[] titles, String path) throws Exception {
        //创建Excel文档对象
        HSSFWorkbook workbook = new HSSFWorkbook();
        //创建sheet
        Sheet sheet = workbook.createSheet();
        //创建第一行
        Row firstRow = sheet.createRow(0);
        //设置标题
        Cell firstCell = firstRow.createCell(0);
        firstCell.setCellValue("学生信息表");
        //设置水平居中
        HSSFCellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        //设置边框
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);
        cellStyle.setBorderTop(BorderStyle.THIN);
        firstCell.setCellStyle(cellStyle);
        /*//设置表头
        String[] title = {"编号", "姓名", "年龄", "性别"};*/
        //设置表头
        Row secondRow = sheet.createRow(1);
        for (int i = 0; i < titles.length; i++) {
            secondRow.createCell(i).setCellValue(titles[i]);
        }
        /*//读取excel获取学生信息
        List<Student> students = readExcel("F:\\IdeaProjects\\student\\src\\main\\resources\\static\\student.xlsx");*/
        //设置单元格内容
        for (int i = 0; i < students.size(); i++) {
            Row row = sheet.createRow(i + 2);
            row.createCell(0).setCellValue(students.get(i).getId());
            row.createCell(1).setCellValue(students.get(i).getName());
            row.createCell(2).setCellValue(students.get(i).getAge());
            row.createCell(3).setCellValue(students.get(i).getSex() == 1 ? "男" : "女");
        }
        //合并单元格
        CellRangeAddress region = new CellRangeAddress(0, 0, 0, 3);
        sheet.addMergedRegion(region);
        //设置列宽倍数
        double multiple = 1.2;
        //自动调整列宽
        for (int i = 0; i < titles.length; i++) {
            sheet.autoSizeColumn(i);
            //解决自动设置列宽中文列宽不足问题
            sheet.setColumnWidth(i, (int) (sheet.getColumnWidth(i) * multiple));
        }
        //设置边框
        for (int i = 1; i < students.size() + 2; i++) {
            Row row = sheet.getRow(i);
            for (int j = 0; j < titles.length; j++) {
                Cell cell = row.getCell(j);
                cell.setCellStyle(cellStyle);
            }
        }
        //输出Excel
        BufferedOutputStream out = new BufferedOutputStream(new FileOutputStream(path));
        workbook.write(out);
        out.close();
    }
}
